﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Oracle.DataAccess.Client;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;

namespace StudentManage.Web.Admin.Management.SysManage.DataSynList
{
    public class DataSyn
    {
        /// <summary>
        /// 检测数据数据是否有差异
        /// </summary>
        /// <param name="newrow">新数据</param>
        /// <param name="oldrow">原数据</param>
        /// <param name="columns">要比较的列集合</param>
        /// <param name="NoField">不比较的列名</param>
        /// <returns>返回是否有相同</returns>
        public bool IsSame(DataRow newrow, DataRow oldrow, DataColumnCollection columns, string NoField)
        {
            bool b = true;
            foreach (DataColumn item in columns)
            {
                if (!newrow.Table.Columns.Contains(item.ColumnName)) continue;

                if (NoField != "" && item.ColumnName == NoField) continue;

                if (oldrow[item.ColumnName].ToString() != newrow[item.ColumnName].ToString())
                {
                    b = false;
                    break;
                }
            }
            return b;
        }

        /// <summary>
        /// 获取oracle数据
        /// </summary>
        /// <param name="sqlstr">查询语句</param>
        /// <returns>返回数据集合</returns>
        public DataTable GetOraceldt(string sqlstr)
        {
            DataTable dt = new DataTable();
            OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["StudentManageMentOracle"].ToString());
            OracleDataAdapter oap = new OracleDataAdapter(sqlstr, con);
            con.Open();
            oap.Fill(dt);
            con.Close();
            return dt;
        }

        /// <summary>
        /// 获取sqlserver数据
        /// </summary>
        /// <param name="sqlstr">查询语句</param>
        /// <returns>返回数据集合</returns>
        public DataTable GetSqldt(string sqlstr)
        {
            DataTable dt = new DataTable();
            SqlConnection consql = new SqlConnection(ConfigurationManager.ConnectionStrings["StudentManageMentConnectionString"].ToString());
            consql.Open();
            SqlDataAdapter sap = new SqlDataAdapter(sqlstr, consql);
            sap.Fill(dt);
            consql.Close();
            return dt;
        }

        /// <summary>
        /// 获取最大用户ID  批量插入使用
        /// </summary>
        /// <returns>返回可用的用户ID</returns>
        public int GetMaxId()
        {
            SqlConnection consql = new SqlConnection(ConfigurationManager.ConnectionStrings["StudentManageMentConnectionString"].ToString());
            consql.Open();
            SqlCommand com = new SqlCommand("select MAX(id) from UserList", consql);
            object o = com.ExecuteScalar();
            consql.Close();
            return o.ToString() == "" ? 1 : Convert.ToInt32(o) + 1;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        public void ExecSqlstr(string sql)
        {
            SqlConnection consql = new SqlConnection(ConfigurationManager.ConnectionStrings["StudentManageMentConnectionString"].ToString());
            consql.Open();
            SqlCommand com = new SqlCommand(sql, consql);
            com.ExecuteNonQuery();
            consql.Close();
        }

        /// <summary>
        /// 获取更新一句
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="KeyColumn">主键列名</param>
        /// <param name="columns">列集合</param>
        /// <param name="row">数据行</param>
        /// <param name="oldcolumns">原始列</param>
        /// <param name="NoField">不更新字段</param>
        /// <returns>返回更新语句</returns>
        private string GetSqlstr(string TableName, string KeyColumn, DataColumnCollection columns, DataRow row, DataColumnCollection oldcolumns, string NoField)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update " + TableName + " set ");
            StringBuilder sb_item = new StringBuilder();
            foreach (DataColumn item in columns)
            {
                if (!oldcolumns.Contains(item.ColumnName)) continue;

                if (item.ColumnName != KeyColumn && item.ColumnName != NoField)
                {
                    if (item.DataType.Name == "String")
                    {
                        sb_item.Append(item.ColumnName + "='" + row[item.ColumnName].ToString() + "',");
                    }
                    else if (item.DataType.Name == "Int64" || item.DataType.Name == "Int32" || item.DataType.Name == "Int16")
                    {
                        if (row[item.ColumnName].ToString() != "")
                        {
                            sb_item.Append(item.ColumnName + "=" + row[item.ColumnName].ToString() + ",");
                        }
                        else
                        {
                            sb_item.Append(item.ColumnName + "=null,");
                        }
                    }
                }
            }
            sb.Append(sb_item.ToString().TrimEnd(','));
            sb.Append("where " + KeyColumn + "='" + row[KeyColumn].ToString() + "'");
            return sb.ToString();
        }

        //使用SqlBulkCopy方式插入数据
        public void SqlBulkCopyInsert(DataTable insertdt, string TableName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["StudentManageMentConnectionString"].ToString();
            DataTable dataTable = insertdt;
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
            sqlBulkCopy.DestinationTableName = TableName;//设置需要插入数据表名
            sqlBulkCopy.BatchSize = dataTable.Rows.Count;//设置批量插入数据的最大数据量的值
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }
            sqlBulkCopy.Close();
            sqlConnection.Close();
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="TableName">需要进行比对的表名</param>
        /// <param name="KeyCloumnName">指定用于比对的主键</param>
        /// <param name="dt_Add">返回需要添加的数据</param>
        /// <param name="dt_Up">返回需要更新的数据</param>
        /// <param name="Idlist">继续需要添加的主键集合</param>
        /// <param name="NoBJColumn">要检测是否有修改的字段</param>
        /// <param name="SQLWhere">针对同步学生和教工时  选择的学院筛选条件</param>
        public void GetData(string TableName, string KeyCloumnName,ref DataTable dt_Add,ref DataTable dt_Up,ref List<String> Idlist, string NoBJColumn = "", string SQLWhere="")
        {
            dt_Add = new DataTable();
            dt_Up = new DataTable();
            Idlist = new List<string>();
            DataTable dt_new = GetOraceldt("select * from " + TableName + SQLWhere);
            DataTable dt_old = GetSqldt("select * from " + TableName);

            dt_Add = dt_new.Clone();
            dt_Up = dt_new.Clone();
            for (int i = 0; i < dt_new.Rows.Count; i++)
            {
                DataRow[] oldrow = dt_old.Select(KeyCloumnName + "='" + dt_new.Rows[i][KeyCloumnName].ToString() + "'");
                if (oldrow.Count() < 1)
                {
                    DataRow row = dt_Add.NewRow();
                    row.ItemArray = dt_new.Rows[i].ItemArray;
                    dt_Add.Rows.Add(row);
                }
                else
                {
                    if (NoBJColumn != "")
                    {
                        if (oldrow[0]["IsEdit"] is DBNull || !Convert.ToBoolean(oldrow[0]["IsEdit"]))
                        {
                            NoBJColumn = "";
                        }
                        else
                        {
                            Idlist.Add(oldrow[0][KeyCloumnName].ToString());
                        }
                    }

                    if (!this.IsSame(dt_new.Rows[i], oldrow[0], dt_old.Columns, NoBJColumn))
                    {
                        DataRow row = dt_Up.NewRow();
                        row.ItemArray = dt_new.Rows[i].ItemArray;
                        dt_Up.Rows.Add(row);
                    }
                }
            }
        }

        //修改数据
        public void UpdateData(string TableName, string KeyCloumnName, DataTable dt_Up, List<String> Idlist, string NoBJColumn = "")
        {
            SqlConnection consql = new SqlConnection(ConfigurationManager.ConnectionStrings["StudentManageMentConnectionString"].ToString());
            consql.Open();
            DataTable dt_old = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter("select top 1 * from " + TableName, consql);
            sda.Fill(dt_old);
            for (int i = 0; i < dt_Up.Rows.Count; i++)
            {
                if (NoBJColumn != "")
                {
                    if (!Idlist.Contains(KeyCloumnName))
                    {
                        NoBJColumn = "";
                    }
                }

                string sqlstr = this.GetSqlstr(TableName, KeyCloumnName, dt_Up.Columns, dt_Up.Rows[i], dt_old.Columns, NoBJColumn);
                SqlCommand com = new SqlCommand(sqlstr, consql);
                com.ExecuteNonQuery();
            }
            consql.Close();
        }
    }
}